package cn.dao;

import cn.comon.DBConnection;
import cn.comon.RedisUtil;
import cn.entity.Employee;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class EmployeeDao implements AutoCloseable {

    private Connection conn;

    public EmployeeDao() {
        conn = new DBConnection().getConn();
    }

    public List<Object[]> selectAll() {
        List<Object[]> data = new ArrayList<>();
        String sql = "SELECT * FROM employee";
        try (Statement sta = conn.createStatement();
             ResultSet rs = sta.executeQuery(sql)) {
            while (rs.next()) {
                Object[] rowData = new Object[7];
                rowData[0] = rs.getString(1);
                rowData[1] = rs.getString(2);
                rowData[2] = rs.getString(3);
                rowData[3] = rs.getString(4);
                rowData[4] = rs.getString(5);
                rowData[5] = rs.getString(6);
                rowData[6] = rs.getString(7);
                data.add(rowData);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return data;
    }

    public Employee checkByEidAndEPas(String eid, String epas) {
        String sql = "SELECT * FROM employee WHERE Eid = ? AND EPW = ?";
        try (PreparedStatement pre = conn.prepareStatement(sql)) {
            pre.setString(1, eid);
            pre.setString(2, epas);
            try (ResultSet re = pre.executeQuery()) {
                if (re.next()) {
                    Employee emp = new Employee();
                    emp.setEid(re.getString(1));
                    emp.setEname(re.getString(2));
                    emp.setEpw(re.getString(3));
                    emp.setElevel(re.getString(4));
                    emp.setEPhone(re.getString(5));
                    emp.setSalary(re.getString(6));
                    emp.setNotes(re.getString(7));
                    return emp;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public void addEmp(List<Employee> ls) throws SQLException {
        String sql = "INSERT INTO employee (Eid, Ename, EPW, Elevel, EPhone, Salary, Notes) VALUES (?, ?, ?, ?, ?, ?, ?)";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            conn.setAutoCommit(false);

            for (Employee emp : ls) {
                if (emp.getEid() == null || emp.getEid().isEmpty()) {
                    ps.setNull(1, java.sql.Types.VARCHAR);
                } else {
                    ps.setString(1, emp.getEid());
                }
                ps.setString(2, emp.getEname());
                ps.setString(3, emp.getEpw());
                ps.setString(4, emp.getElevel());
                ps.setString(5, emp.getEPhone());
                ps.setString(6, emp.getSalary());
                ps.setString(7, emp.getNotes());
                ps.addBatch();
            }

            ps.executeBatch();
            conn.commit();

            try (RedisUtil redis = new RedisUtil()) {
                for (Employee emp : ls) {
                    if (emp.getEid() != null && !emp.getEid().isEmpty()) {
                        redis.set(emp.getEid(), emp.getEpw());
                    }
                }
            }
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException rollbackEx) {
                rollbackEx.printStackTrace();
            }
            throw e;  // 抛出异常给调用者
        }
    }


    public void deleteEmp(String eid) {
        String sql = "DELETE FROM employee WHERE Eid = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, eid);
            ps.executeUpdate();
            try (RedisUtil redis = new RedisUtil()) {
                redis.delete(eid);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void updateEmp(Employee emp, String originalEid) throws SQLException {
        String sql = "UPDATE employee SET Eid = ?, Ename = ?, EPW = ?, Elevel = ?, EPhone = ?, Salary = ?, Notes = ? WHERE Eid = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, emp.getEid());
            ps.setString(2, emp.getEname());
            ps.setString(3, emp.getEpw());
            ps.setString(4, emp.getElevel());
            ps.setString(5, emp.getEPhone());
            ps.setString(6, emp.getSalary());
            ps.setString(7, emp.getNotes());
            ps.setString(8, originalEid);
            ps.executeUpdate();

            try (RedisUtil redis = new RedisUtil()) {
                redis.delete(originalEid);
                redis.set(emp.getEid(), emp.getEpw());
            }
        } catch (SQLException e) {
            throw e;  // 抛出异常给调用者
        }
    }


    public List<Employee> queryEmployees(Employee criteria) {
        List<Employee> employees = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM employee WHERE 1=1");
        if (criteria.getEid() != null && !criteria.getEid().isEmpty()) {
            sql.append(" AND Eid = ?");
        }
        if (criteria.getEname() != null && !criteria.getEname().isEmpty()) {
            sql.append(" AND Ename = ?");
        }
        if (criteria.getEpw() != null && !criteria.getEpw().isEmpty()) {
            sql.append(" AND EPW = ?");
        }
        if (criteria.getElevel() != null && !criteria.getElevel().isEmpty()) {
            sql.append(" AND Elevel = ?");
        }
        if (criteria.getEPhone() != null && !criteria.getEPhone().isEmpty()) {
            sql.append(" AND EPhone = ?");
        }
        if (criteria.getSalary() != null && !criteria.getSalary().isEmpty()) {
            sql.append(" AND Salary = ?");
        }
        if (criteria.getNotes() != null && !criteria.getNotes().isEmpty()) {
            sql.append(" AND Notes = ?");
        }
        try (PreparedStatement ps = conn.prepareStatement(sql.toString())) {
            int index = 1;
            if (criteria.getEid() != null && !criteria.getEid().isEmpty()) {
                ps.setString(index++, criteria.getEid());
            }
            if (criteria.getEname() != null && !criteria.getEname().isEmpty()) {
                ps.setString(index++, criteria.getEname());
            }
            if (criteria.getEpw() != null && !criteria.getEpw().isEmpty()) {
                ps.setString(index++, criteria.getEpw());
            }
            if (criteria.getElevel() != null && !criteria.getElevel().isEmpty()) {
                ps.setString(index++, criteria.getElevel());
            }
            if (criteria.getEPhone() != null && !criteria.getEPhone().isEmpty()) {
                ps.setString(index++, criteria.getEPhone());
            }
            if (criteria.getSalary() != null && !criteria.getSalary().isEmpty()) {
                ps.setString(index++, criteria.getSalary());
            }
            if (criteria.getNotes() != null && !criteria.getNotes().isEmpty()) {
                ps.setString(index++, criteria.getNotes());
            }
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Employee emp = new Employee();
                    emp.setEid(rs.getString(1));
                    emp.setEname(rs.getString(2));
                    emp.setEpw(rs.getString(3));
                    emp.setElevel(rs.getString(4));
                    emp.setEPhone(rs.getString(5));
                    emp.setSalary(rs.getString(6));
                    emp.setNotes(rs.getString(7));
                    employees.add(emp);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return employees;
    }

    public List<Employee> queryEmployeesByKeyword(String keyword) {
        List<Employee> employees = new ArrayList<>();
        String sql = "SELECT * FROM employee WHERE eid LIKE ? OR ename LIKE ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, "%" + keyword + "%");
            ps.setString(2, "%" + keyword + "%");
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Employee emp = new Employee();
                    emp.setEid(rs.getString(1));
                    emp.setEname(rs.getString(2));
                    emp.setEpw(rs.getString(3));
                    emp.setElevel(rs.getString(4));
                    emp.setEPhone(rs.getString(5));
                    emp.setSalary(rs.getString(6));
                    emp.setNotes(rs.getString(7));
                    employees.add(emp);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return employees;
    }

    public List<Object[]> queryEmployeePayData() {
        List<Object[]> data = new ArrayList<>();
        String sql = "SELECT e.Eid, e.Ename, COUNT(DISTINCT d.Gid) AS goodsTypes, SUM(d.DCount) AS totalCount, SUM(d.GoodsTotal) AS totalPrice " +
                "FROM Employee e " +
                "JOIN PayMain p ON e.Eid = p.Eid " +
                "JOIN PayDetail d ON p.Mid = d.Mid " +
                "GROUP BY e.Eid, e.Ename";
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                Object[] rowData = new Object[5];
                rowData[0] = rs.getString("Eid");
                rowData[1] = rs.getString("Ename");
                rowData[2] = rs.getInt("goodsTypes");
                rowData[3] = rs.getInt("totalCount");
                rowData[4] = rs.getDouble("totalPrice");
                data.add(rowData);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return data;
    }



    @Override
    public void close() {
        try {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
